import pandas as pd

def calculate_water_flow_table():
    target_years = [2016, 2017, 2018, 2019, 2020, 2021]
    file_path = 'ori_message.xlsx'
    try:
        df_all = pd.read_excel(file_path, sheet_name=[str(year) for year in target_years])
    except FileNotFoundError:
        print(f"错误：文件 {file_path} 未找到。")
        return
    except Exception as e:
        print(f"错误：读取文件时发生未知错误：{e}")
        return

    year_water = {}

    for year in target_years:
        df = df_all[str(year)]
        # 去除列名中的不可见字符
        df.columns = [col.strip() for col in df.columns]

        # 检查流量列和时间列是否存在
        flow_column = '流量(m3/s)'
        time_column = '时间'
        if flow_column not in df.columns or time_column not in df.columns:
            print(f"警告：{year} 年数据中缺少必要列（流量或时间），无法计算！")
            continue

        # 提取时间列中的小时数
        df['小时'] = df[time_column].astype(str).apply(lambda x: int(x.split(':')[0]))

        # 计算时段长度（秒）
        delta_t_seconds = []
        for i in range(len(df)):
            if i == 0:
                # 第一行，时段从 0 点开始
                delta_t = df.iloc[i]['小时'] * 3600
            else:
                hour_diff = df.iloc[i]['小时'] - df.iloc[i - 1]['小时']
                if hour_diff > 0:
                    delta_t = hour_diff * 3600
                else:
                    delta_t = (hour_diff + 24) * 3600
            delta_t_seconds.append(delta_t)
        df['时段长度(秒)'] = delta_t_seconds

        # 计算年总水流量
        total_flow = (df['时段长度(秒)'] * df[flow_column]).sum()
        total_flow_billion_cubic_meter = total_flow * 1e-8  # 转换为亿立方米
        year_water[year] = round(total_flow_billion_cubic_meter, 3)

    # 输出表 5 格式结果
    print("\n2016 - 2021 年的年总水流量表")
    print("| 日期   | 2016      | 2017      | 2018      | 2019      | 2020      | 2021      |")
    print("|--------|-----------|-----------|-----------|-----------|-----------|-----------|")
    print("| 年总水流量(亿m³) | {:.3f} | {:.3f} | {:.3f} | {:.3f} | {:.3f} | {:.3f} |".format(
        year_water[2016], year_water[2017], year_water[2018],
        year_water[2019], year_water[2020], year_water[2021]
    ))

# 执行函数
calculate_water_flow_table()